package com.kaikeba.dao.impl;

import com.kaikeba.bean.Courier;
import com.kaikeba.bean.User;
import com.kaikeba.dao.BaseUserDao;
import com.kaikeba.exception.DuplicateCodeException;
import com.kaikeba.util.DruidUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @description:
 * @author: ManolinCoder
 * @time: 2021/9/4
 */
public class UserDaoMysql implements BaseUserDao {
    //用于查询数据库中的全部用户信息（总数+新增）,ustatus为1的不算
    public static final String U_CONSOLE = "SELECT " +
            "COUNT(ID) data_size," +
            "COUNT(TO_DAYS(UINTIME)=TO_DAYS(NOW()) OR NULL) data_day" +
            " FROM EUSER WHERE USTATUS!=1";
    //用于查询所有用户信息(未分页),ustatus为1的不算
    public static final String U_FIND_ALL = "SELECT * FROM EUSER WHERE USTATUS!=1";
    //用于查询所有用户信息(分页),ustatus为1的不算
    public static final String U_FIND_LIMIT = "SELECT * FROM EUSER WHERE USTATUS!=1 LIMIT ?,?";
    //根据用户电话号码，查询用户信息,ustatus为1的不算
    public static final String U_FIND_BY_PHONENUMBER = "SELECT * FROM EUSER WHERE UPHONENUMBER=? AND USTATUS!=1";
    //根据用户电话号码，查询用户信息
    public static final String U_FIND_BY_PHONENUMBER_INCLUDE_STATUS_IS_ONE = "SELECT * FROM EUSER WHERE UPHONENUMBER=?";
    //根据用户id，查询用户信息,ustatus为1的不算
    public static final String U_FIND_BY_ID = "SELECT * FROM EUSER WHERE ID=? AND USTATUS!=1";
    //用户信息的录入
    public static final String U_INSERT = "INSERT INTO EUSER (UNAME,UPHONENUMBER,UIDCARDNUM,UPASSWORD,UINTIME,ULASTLOGINTIME,USTATUS) VALUES(?,?,?,?,NOW(),NOW(),0)";
    //用户信息的修改
    public static final String U_UPDATE = "UPDATE EUSER SET UNAME=?,UPHONENUMBER=?,UIDCARDNUM=?,UPASSWORD=?,ULASTLOGINTIME=? WHERE ID=?";
    //用户信息的删除,假删除设置ustatus为1
    public static final String U_DELETE = "UPDATE EUSER SET USTATUS=1 WHERE ID=?";

    /**
     * @return java.util.Map<java.lang.String, java.lang.Integer>
     * @Author LC
     * @Description //TODO 查询用户表中所有记录包括总数和新增（新增就是注册时间与当前时间相同）,ustatus为1的不算
     * @Date 15:00 2021/9/4
     * @Param []
     **/
    @Override
    public Map<String, Integer> console() {
        Map<String,Integer> data = new HashMap<>();
        //1.获取数据库链接
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        ResultSet result = null;
        try {
            //2.预编译SQL语句
            state = conn.prepareStatement(U_CONSOLE);
            //3.执行SQL语句
            result = state.executeQuery();
            //4.获取执行结果
            if (result.next()){
                int data_size = result.getInt("data_size");
                int data_day = result.getInt("data_day");
                data.put("data_size",data_size);
                data.put("data_day",data_day);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //5.资源的释放
            DruidUtil.close(conn,state,result);
        }
        return data;
    }

    /**
     * @param limit
     * @param offset
     * @param pageNumber
     * @return 用户信息集合
     * @Author LC
     * @Description //TODO 查询所有用户信息,ustatus为1的不算
     * @Date 15:01 2021/9/4
     * @Param [limit 是否分页的标记，true表示分页。false表示查询所有快递, offset SQL语句的起始索引, pageNumber 每一页查询的数量]
     */
    @Override
    public List<User> findAll(boolean limit, int offset, int pageNumber) {
        ArrayList<User> data = new ArrayList<>();
        //1.获取数据库链接
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        ResultSet result = null;
        try {
            //2.预编译SQL语句
            if (limit){
                state = conn.prepareStatement(U_FIND_LIMIT);
                //3.填充参数（可选）
                state.setInt(1,offset);
                state.setInt(2,pageNumber);
            }else {
                state = conn.prepareStatement(U_FIND_ALL);
            }
            //4.执行SQL语句
            result = state.executeQuery();
            //5.获取执行结果
            while (result.next()){
                int id = result.getInt("id");
                String uname = result.getString("uname");
                String uPhoneNumber = result.getString("uphonenumber");
                String uIdCardNum = result.getString("uidcardnum");
                String uPassword = result.getString("upassword");
                Date uInTime = result.getDate("uintime");
                Date uLastLoginTime = result.getDate("ulastlogintime");
                int uStatus = result.getInt("ustatus");
                User user = new User(id,uname,uPhoneNumber,uIdCardNum,uPassword,uInTime,uLastLoginTime,uStatus);
                data.add(user);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //6.资源的释放
            DruidUtil.close(conn,state,result);
        }
        return data;
    }

    /**
     * @param phoneNumber
     * @return 查询的用户信息，手机号不存在时返回null
     * @Author LC
     * @Description //TODO 根据用户手机号查找对应用户,ustatus为1的不算
     * @Date 15:03 2021/9/4
     * @Param [phoneNumber 用户手机号]
     */
    @Override
    public User findByPhoneNumber(String phoneNumber) {
        //1.获取数据库链接
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        ResultSet result = null;
        try {
            //2.预编译SQL语句
            state = conn.prepareStatement(U_FIND_BY_PHONENUMBER);
            //3.填充参数（可选）
            state.setString(1,phoneNumber);
            //4.执行SQL语句
            result = state.executeQuery();
            //5.获取执行结果
            if (result.next()){
                int id = result.getInt("id");
                String uname = result.getString("uname");
                String uPhoneNumber = result.getString("uphonenumber");
                String uIdCardNum = result.getString("uidcardnum");
                String uPassword = result.getString("upassword");
                Date uInTime = result.getDate("uintime");
                Date uLastLoginTime = result.getDate("ulastlogintime");
                int uStatus = result.getInt("ustatus");
                User user = new User(id,uname,uPhoneNumber,uIdCardNum,uPassword,uInTime,uLastLoginTime,uStatus);
                return user;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //6.资源的释放
            DruidUtil.close(conn,state,result);
        }
        return null;
    }

    /**
     * @param phoneNumber
     * @return 查询的用户信息，手机号不存在时返回null
     * @Author LC
     * @Description //TODO 根据用户手机号查找对应用户,即使ustatus为1也查找
     * @Date 15:11 2021/9/6
     * @Param [phoneNumber 用户手机号]
     */
    @Override
    public User findUserIncludeStatusIsOneByPhoneNum(String phoneNumber) {
        //1.获取数据库链接
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        ResultSet result = null;
        try {
            //2.预编译SQL语句
            state = conn.prepareStatement(U_FIND_BY_PHONENUMBER_INCLUDE_STATUS_IS_ONE);
            //3.填充参数（可选）
            state.setString(1,phoneNumber);
            //4.执行SQL语句
            result = state.executeQuery();
            //5.获取执行结果
            if (result.next()){
                int id = result.getInt("id");
                String uname = result.getString("uname");
                String uPhoneNumber = result.getString("uphonenumber");
                String uIdCardNum = result.getString("uidcardnum");
                String uPassword = result.getString("upassword");
                Date uInTime = result.getDate("uintime");
                Date uLastLoginTime = result.getDate("ulastlogintime");
                int uStatus = result.getInt("ustatus");
                User user = new User(id,uname,uPhoneNumber,uIdCardNum,uPassword,uInTime,uLastLoginTime,uStatus);
                return user;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //6.资源的释放
            DruidUtil.close(conn,state,result);
        }
        return null;
    }

    /**
     * @param id
     * @return 查询的用户信息，id不存在时返回null
     * @Author LC
     * @Description //TODO 根据用户主键id查找对应用户,ustatus为1的不算
     * @Date 15:04 2021/9/4
     * @Param [id 用户主键id]
     */
    @Override
    public User findById(int id) {
        //1.获取数据库链接
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        ResultSet result = null;
        try {
            //2.预编译SQL语句
            state = conn.prepareStatement(U_FIND_BY_ID);
            //3.填充参数（可选）
            state.setInt(1,id);
            //4.执行SQL语句
            result = state.executeQuery();
            //5.获取执行结果
            if (result.next()){
                String uname = result.getString("uname");
                String uPhoneNumber = result.getString("uphonenumber");
                String uIdCardNum = result.getString("uidcardnum");
                String uPassword = result.getString("upassword");
                Date uInTime = result.getDate("uintime");
                Date uLastLoginTime = result.getDate("ulastlogintime");
                int uStatus = result.getInt("ustatus");
                User user = new User(id,uname,uPhoneNumber,uIdCardNum,uPassword,uInTime,uLastLoginTime,uStatus);
                return user;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //6.资源的释放
            DruidUtil.close(conn,state,result);
        }
        return null;
    }

    /**
     * @param u
     * @return 录入的结果, true表示成功, false表示失败
     * @Author LC
     * @Description //TODO 用户信息的录入,注意手机号不能重复
     * @Date 15:05 2021/9/4
     * @Param [u 用户信息对象]
     */
    @Override
    public boolean insert(User u) throws DuplicateCodeException {
        //1.获取数据库链接
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        try {
            //2.预编译SQL语句
            state = conn.prepareStatement(U_INSERT);
            //3.填充参数
            state.setString(1,u.getUname());
            state.setString(2,u.getuPhoneNumber());
            state.setString(3,u.getuIdCardNum());
            state.setString(4,u.getuPassword());
            //4.执行SQL语句，并获取执行结果
            return state.executeUpdate()>0?true:false;
        } catch (SQLException e) {
            if (e.getMessage().endsWith("for key 'phonenumber'")){
                //因为快递员电话号码重复而出现异常
                DuplicateCodeException e1 = new DuplicateCodeException(e.getMessage());
                throw e1;
            }else {
                e.printStackTrace();
            }
        } finally {
            //5.资源的释放
            DruidUtil.close(conn,state,null);
        }
        return false;
    }

    /**
     * @param id
     * @param newu
     * @return 更新的结果, true表示成功, false表示失败
     * @Author LC
     * @Description //TODO 用户信息的更新
     * @Date 15:06 2021/9/4
     * @Param [id 要更新的用户d, newu 新的用户信息对象]
     */
    @Override
    public boolean update(int id, User newu) {
        //1.获取数据库链接
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        try {
            //2.预编译SQL语句
            state = conn.prepareStatement(U_UPDATE);
            state.setString(1,newu.getUname());
            state.setString(2,newu.getuPhoneNumber());
            state.setString(3,newu.getuIdCardNum());
            state.setString(4,newu.getuPassword());
            //将java.util.date转换为java.sql.date
            java.sql.Date date = new java.sql.Date(newu.getuLastLoginTime().getTime());
            state.setDate(5,date);
            state.setInt(6,id);
            return state.executeUpdate()>0?true:false;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //3.资源的释放
            DruidUtil.close(conn,state,null);
        }
        return false;
    }

    /**
     * @param id
     * @return 删除的结果, true表示成功, false表示失败
     * @Author LC
     * @Description //TODO 根据用户的id删除相应数据（假删除）
     * @Date 15:07 2021/9/4
     * @Param [id 用户的id]
     */
    @Override
    public boolean delete(int id) {
        //1.获取数据库链接
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        try {
            //2.预编译SQL语句
            state = conn.prepareStatement(U_DELETE);
            state.setInt(1,id);
            return state.executeUpdate()>0?true:false;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //3.资源的释放
            DruidUtil.close(conn,state,null);
        }
        return false;
    }
}
